from osv import osv, fields

class kderp_reports_account_receivable_summary_daily(osv.osv):
     _name = "kderp.reports.account.receivable.summary.daily"
     _description ="KDERP Reports Account Receivable Summary Daily"
     _auto = False
     _columns = {
                   # "id":fields.many2one("sale.order","Quotation"),
                    "ar_year":fields.integer("Year",select=1),
                    "ar_detail_ids":fields.one2many('kderp.reports.account.receivable.detail.daily','ar_year','Details'),
                    "ar_amount_idr":fields.float("AR Amt. IDR"),
                    "ar_retention_idr":fields.float("AR Retntion IDR"),
                    "ar_vat_idr":fields.float("AR VAT IDR"),
                    
                    "ar_amount_usd":fields.float("AR Amt. USD"),
                    "ar_retention_usd":fields.float("AR Retntion USD"),
                    "ar_vat_usd":fields.float("AR VAT USD"),
                    
                    "ar_amount_jpy":fields.float("AR Amt. JPY"),
                    "ar_retention_jpy":fields.float("AR Retntion JPY"),
                    "ar_vat_jpy":fields.float("AR VAT JPY"),
                    
                    "ar_amount_sgd":fields.float("AR Amt. SGD"),
                    "ar_retention_sgd":fields.float("AR Retntion SGD"),
                    "ar_vat_sgd":fields.float("AR VAT SGD"),
                    
                    "ar_amount_eur":fields.float("AR Amt. EUR"),
                    "ar_retention_eur":fields.float("AR Retntion EUR"),
                    "ar_vat_eur":fields.float("AR VAT EUR"),
                 }
     def init(self, cr):
         cr.execute("""create or replace view kderp_reports_account_receivable_summary_daily as 
                        Select
                            ar_year as id,
                            ar_year,
                            --IDR
                            sum(case when code='IDR' then ar_amount else 0 end) as ar_amount_idr,
                            sum(case when code='IDR' then ar_retention else 0 end) as ar_retention_idr,
                            sum(case when code='IDR' then ar_vat else 0 end) as ar_vat_idr,
                            --USD
                            sum(case when code='USD' then ar_amount else 0 end) as ar_amount_usd,
                            sum(case when code='USD' then ar_retention else 0 end) as ar_retention_usd,
                            sum(case when code='USD' then ar_vat else 0 end) as ar_vat_usd,
                            --JPY
                            sum(case when code='JPY' then ar_amount else 0 end) as ar_amount_jpy,
                            sum(case when code='JPY' then ar_retention else 0 end) as ar_retention_jpy,
                            sum(case when code='JPY' then ar_vat else 0 end) as ar_vat_jpy,
                            --SGD
                            sum(case when code='SGD' then ar_amount else 0 end) as ar_amount_sgd,
                            sum(case when code='SGD' then ar_retention else 0 end) as ar_retention_sgd,
                            sum(case when code='SGD' then ar_vat else 0 end) as ar_vat_sgd,    
                            --EUR
                            sum(case when code='EUR' then ar_amount else 0 end) as ar_amount_eur,
                            sum(case when code='EUR' then ar_retention else 0 end) as ar_retention_eur,
                            sum(case when code='EUR' then ar_vat else 0 end) as ar_vat_eur
                        from
                            (Select 
                                date_part('year',date) as ar_year,
                                kpfc.id,
                                sum(case when kptc.type!='re' then
                                    progress_amount+adv_payment+others+retention-with_holding_amount
                                else
                                    0
                                end) as ar_amount,
                                sum(case when kptc.type='re' then
                                    retention-with_holding_amount
                                else
                                    0
                                end) as ar_retention,
                                sum(kdvn_amount_tax) ar_vat,
                                kc.code
                            from
                                kdvn_payment_from_client kpfc 
                            left join
                                kderp_contract_payment_term_line kcptl on payment_term_line_id = kcptl.id
                            left join
                                kderp_payment_term_code kptc on  term_id = kptc.id
                            left join
                                kderp_currency kc on currency_id = kc.id
                            where 
                                state not in ('draft','cancel') and 
                                not exists(Select id from kdvn_received kr where kr.payment_id=kpfc.id)
                            Group by
                                date_part('year',date),
                                kc.code,kpfc.id) vwaccount_receivable_daily
                        Group by
                            ar_year;""")
kderp_reports_account_receivable_summary_daily()


class kderp_reports_account_receivable_detail_daily(osv.osv):
     _name = "kderp.reports.account.receivable.detail.daily"
     _description ="KDERP Reports Account Receivable Detail Daily"
     _auto = False
     _columns = {
                   # "id":fields.many2one("sale.order","Quotation"),
                    "ar_year":fields.integer("Year",select=1),
                    'payment_id':fields.many2one('kdvn.payment.from.client','Payment'),
                    'currency_id':fields.many2one('kderp.currency','Cur.',link=1),
                    'ar_amount':fields.float('AR Amt.'),
                    'ar_retention_amount':fields.float('AR Retention'),
                    'ar_vat_amount':fields.float('AR VAT'),
                 }
     def init(self, cr):
         cr.execute("""create or replace view kderp_reports_account_receivable_detail_daily  as
                        Select 
                        kpfc.id,
                        date_part('year',date) as ar_year,
                        kpfc.id as payment_id,
                        kpfc.currency_id,
                        sum(case when kptc.type!='re' then
                            progress_amount+adv_payment+retention+others-with_holding_amount
                        else
                            0
                        end) as ar_amount,
                        sum(case when kptc.type='re' then
                            retention-with_holding_amount
                        else
                            0
                        end) as ar_retention_amount,
                        sum(kdvn_amount_tax) ar_vat_amount
                    from
                        kdvn_payment_from_client kpfc 
                    left join
                        kderp_contract_payment_term_line kcptl on payment_term_line_id = kcptl.id
                    left join
                        kderp_payment_term_code kptc on  term_id = kptc.id
                    left join
                        kderp_currency kc on currency_id = kc.id
                    where 
                        state not in ('draft','cancel') and 
                        not exists(Select id from kdvn_received kr where kr.payment_id=kpfc.id)
                    Group by
                        date_part('year',date),
                        kc.code,kpfc.id""")     
kderp_reports_account_receivable_detail_daily()